
#http://www.blog.pythonlibrary.org/2010/02/03/another-step-by-step-sqlalchemy-tutorial-part-1-of-2/
#http://www.rmunn.com/sqlalchemy-tutorial/tutorial.html

from sqlalchemy import create_engine
from sqlalchemy import MetaData, Column, Table, ForeignKey
from sqlalchemy import Integer, String
from sqlalchemy.sql import select
from sqlalchemy.sql import and_
import os

if (os.path.exists('bdd-sqlalchemy.db')):
    os.unlink('bdd-sqlalchemy.db')

engine = create_engine('sqlite:///bdd-sqlalchemy.db',
                       echo=True)
 
metadata = MetaData(bind=engine)
 
users_table = Table('users', metadata,
                    Column('id', Integer, primary_key=True),
                    Column('name', String(40)),
                    Column('age', Integer),
                    Column('password', String),
                    )
''' 
addresses_table = Table('addresses', metadata,
                        Column('id', Integer, primary_key=True),
                        Column('user_id', None, ForeignKey('users.id')),
                        Column('email_address', String, nullable=False)                            
                        )
''' 
# create tables in database
metadata.create_all()

###################################################################################


# create an Insert object
ins = users_table.insert()
# add values to the Insert object
new_user = ins.values(name="Joe", age=20, password="pass")
 
# create a database connection
conn = engine.connect()
# add user to database by executing SQL
conn.execute(new_user)

# a connectionless way to Insert a user
ins = users_table.insert()
result = engine.execute(ins, name="Shinji", age=15, password="nihongo")
 
# another connectionless Insert
result = users_table.insert().execute(name="Martha", age=45, password="dingbat")

conn.execute(users_table.insert(), [
    {"name": "Ted", "age":10, "password":"dink"},
    {"name": "Asahina", "age":25, "password":"nippon"},
    {"name": "Evan", "age":40, "password":"macaca"}
])


#############################################################################################
# The following is the equivalent to 
# SELECT * FROM users WHERE id > 3
s = select([users_table], users_table.c.id > 3)
 
# You can use the "and_" module to AND multiple fields together
#s = select(and_(users_table.c.name=="Martha", users_table.c.age < 25))

#s = select([users_table])
result = s.execute()
 
for row in result:
    print row





#############################################################################################